﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using DingTalkAgent.DBUtility;
namespace DingTalkAgent.SqlData
{
    //T_Operate
    public partial class Operate
    {
        #region	 T_Operate

        ///<summary>
        /// Exist
        ///</summary>
        public bool Exists(int P_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_Operate");
            strSql.Append(" where ");
            strSql.Append(" P_ID = @P_ID  ");
            SqlParameter[] parameters = {
                    new SqlParameter("@P_ID", SqlDbType.Int,4)
            };
            parameters[0].Value = P_ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条T_Operate数据
        /// </summary>
        public int Add(DingTalkAgent.Model.Operate model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Operate(");
            strSql.Append("Site_ID,User_ID,Opt_IP,Op_Type,Op_Msg,Op_Time,LoginName,UnitCode");
            strSql.Append(") values (");
            strSql.Append("@Site_ID,@User_ID,@Opt_IP,@Op_Type,@Op_Msg,@Op_Time,@LoginName,@UnitCode");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                        new SqlParameter("@Site_ID", SqlDbType.Int,4) ,
                        new SqlParameter("@User_ID", SqlDbType.VarChar,50) ,
                        new SqlParameter("@Opt_IP", SqlDbType.VarChar,30) ,
                        new SqlParameter("@Op_Type", SqlDbType.VarChar,40) ,
                        new SqlParameter("@Op_Msg", SqlDbType.VarChar,2000) ,
                        new SqlParameter("@Op_Time", SqlDbType.DateTime) ,
                        new SqlParameter("@LoginName", SqlDbType.VarChar,50) ,
                        new SqlParameter("@UnitCode", SqlDbType.VarChar,50)

            };

            parameters[0].Value = model.Site_ID;
            parameters[1].Value = model.User_ID;
            parameters[2].Value = model.Opt_IP;
            parameters[3].Value = model.Op_Type;
            parameters[4].Value = model.Op_Msg;
            parameters[5].Value = model.Op_Time;
            parameters[6].Value = model.LoginName;
            parameters[7].Value = model.UnitCode;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条T_Operate数据
        /// </summary>
        public bool Update(DingTalkAgent.Model.Operate model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Operate set ");

            strSql.Append(" Site_ID = @Site_ID , ");
            strSql.Append(" User_ID = @User_ID , ");
            strSql.Append(" Opt_IP = @Opt_IP , ");
            strSql.Append(" Op_Type = @Op_Type , ");
            strSql.Append(" Op_Msg = @Op_Msg , ");
            strSql.Append(" Op_Time = @Op_Time , ");
            strSql.Append(" LoginName = @LoginName , ");
            strSql.Append(" UnitCode = @UnitCode  ");
            strSql.Append(" where P_ID=@P_ID ");

            SqlParameter[] parameters = {
                        new SqlParameter("@P_ID", SqlDbType.Int,4) ,
                        new SqlParameter("@Site_ID", SqlDbType.Int,4) ,
                        new SqlParameter("@User_ID", SqlDbType.VarChar,50) ,
                        new SqlParameter("@Opt_IP", SqlDbType.VarChar,30) ,
                        new SqlParameter("@Op_Type", SqlDbType.VarChar,40) ,
                        new SqlParameter("@Op_Msg", SqlDbType.VarChar,2000) ,
                        new SqlParameter("@Op_Time", SqlDbType.DateTime) ,
                        new SqlParameter("@LoginName", SqlDbType.VarChar,50) ,
                        new SqlParameter("@UnitCode", SqlDbType.VarChar,50)

            };

            parameters[0].Value = model.P_ID;
            parameters[1].Value = model.Site_ID;
            parameters[2].Value = model.User_ID;
            parameters[3].Value = model.Opt_IP;
            parameters[4].Value = model.Op_Type;
            parameters[5].Value = model.Op_Msg;
            parameters[6].Value = model.Op_Time;
            parameters[7].Value = model.LoginName;
            parameters[8].Value = model.UnitCode;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条T_Operate数据
        /// </summary>
        public bool Delete(int P_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_Operate ");
            strSql.Append(" where P_ID=@P_ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@P_ID", SqlDbType.Int,4)
            };
            parameters[0].Value = P_ID;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条T_Operate数据
        /// </summary>
        public bool Deletes(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_Operate ");
            strSql.Append(" where ID in (" + ids + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DingTalkAgent.Model.Operate GetModel(int P_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select P_ID, Site_ID, User_ID, Opt_IP, Op_Type, Op_Msg, Op_Time, LoginName, UnitCode  ");
            strSql.Append("  from T_Operate ");
            strSql.Append(" where P_ID=@P_ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@P_ID", SqlDbType.Int,4)
            };
            parameters[0].Value = P_ID;


            DingTalkAgent.Model.Operate model = new DingTalkAgent.Model.Operate();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["P_ID"].ToString() != "")
                {
                    model.P_ID = int.Parse(ds.Tables[0].Rows[0]["P_ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Site_ID"].ToString() != "")
                {
                    model.Site_ID = int.Parse(ds.Tables[0].Rows[0]["Site_ID"].ToString());
                }
                model.User_ID = ds.Tables[0].Rows[0]["User_ID"].ToString();
                model.Opt_IP = ds.Tables[0].Rows[0]["Opt_IP"].ToString();
                model.Op_Type = ds.Tables[0].Rows[0]["Op_Type"].ToString();
                model.Op_Msg = ds.Tables[0].Rows[0]["Op_Msg"].ToString();
                if (ds.Tables[0].Rows[0]["Op_Time"].ToString() != "")
                {
                    model.Op_Time = DateTime.Parse(ds.Tables[0].Rows[0]["Op_Time"].ToString());
                }
                model.LoginName = ds.Tables[0].Rows[0]["LoginName"].ToString();
                model.UnitCode = ds.Tables[0].Rows[0]["UnitCode"].ToString();

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据T_Operate列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM T_Operate ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据T_Operate列表
        /// </summary>
        public IEnumerable<DingTalkAgent.Model.Operate> IEnumGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select P_ID, Site_ID, User_ID, Opt_IP, Op_Type, Op_Msg, Op_Time, LoginName, UnitCode ");
            strSql.Append(" FROM T_Operate ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            DingTalkAgent.Model.Operate model;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                model = new DingTalkAgent.Model.Operate();
                if (dr["P_ID"].ToString() != "")
                {
                    model.P_ID = int.Parse(dr["P_ID"].ToString());
                }
                if (dr["Site_ID"].ToString() != "")
                {
                    model.Site_ID = int.Parse(dr["Site_ID"].ToString());
                }
                model.User_ID = dr["User_ID"].ToString();
                model.Opt_IP = dr["Opt_IP"].ToString();
                model.Op_Type = dr["Op_Type"].ToString();
                model.Op_Msg = dr["Op_Msg"].ToString();
                if (dr["Op_Time"].ToString() != "")
                {
                    model.Op_Time = DateTime.Parse(dr["Op_Time"].ToString());
                }
                model.LoginName = dr["LoginName"].ToString();
                model.UnitCode = dr["UnitCode"].ToString();

                yield return model;
            }
        }
        /// <summary>
        /// 获得数据 T_Operate分页列表
        /// </summary>
        /// <param name="PageIndex">页码</param>
        /// <param name="PageSize">页大小</param>
        /// <param name="total">总记录数</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public List<DingTalkAgent.Model.Operate> GetPageList(int PageIndex, int PageSize, out int total, string keyword, string UnitCode = "")
        {
            int Skip = (PageIndex - 1) * PageSize;
            //count
            StringBuilder strSqlCount = new StringBuilder();
            strSqlCount.Append("Select @total=count(P_ID) From T_Operate ");
            //data
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Top " + PageSize + " * from (");
            strSql.Append("select  row_number() OVER (ORDER BY P_ID Desc) AS row, P_ID, Site_ID, Opt_IP, Op_Time, Op_type, Op_Msg, User_ID, LoginName,UnitCode ");
            strSql.Append(" FROM  T_Operate ");
            //where
            StringBuilder Where = new StringBuilder();
            Where.Append(" WHERE P_ID>0 ");

            if (keyword.Trim() != "")
            {
                Where.Append(" and (P_ID like @keyword or LoginName like @keyword) ");
            }
            if (!string.IsNullOrEmpty(UnitCode))
            {
                Where.AppendFormat(" and UnitCode='{0}' ", UnitCode);
            }

            strSqlCount.Append(Where.ToString());
            strSql.Insert(0, strSqlCount.ToString() + ";");
            strSql.Append(Where.ToString());
            strSql.Append(") as a ");
            strSql.Append(" Where a.row>" + Skip);

            //parameters
            SqlParameter[] parameters = {
                    new SqlParameter("@total", SqlDbType.Int,4),
                    new SqlParameter("@keyword", SqlDbType.VarChar,64)
                    };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = "%" + keyword.Trim() + "%";

            List<DingTalkAgent.Model.Operate> list = new List<DingTalkAgent.Model.Operate>();
            DingTalkAgent.Model.Operate model = new DingTalkAgent.Model.Operate();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            total = Convert.ToInt32(parameters[0].Value);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                model = new DingTalkAgent.Model.Operate();
                if (dr["P_ID"].ToString() != "")
                {
                    model.P_ID = int.Parse(dr["P_ID"].ToString());
                }
                if (dr["Site_ID"].ToString() != "")
                {
                    model.Site_ID = int.Parse(dr["Site_ID"].ToString());
                }
                model.Opt_IP = dr["Opt_IP"].ToString();
                if (dr["Op_Time"].ToString() != "")
                {
                    model.Op_Time = DateTime.Parse(dr["Op_Time"].ToString());
                }
                model.Op_Type = dr["Op_type"].ToString();
                model.Op_Msg = dr["Op_Msg"].ToString();
                if (dr["User_ID"].ToString() != "")
                {
                    model.User_ID = dr["User_ID"].ToString();
                }
                model.LoginName = dr["LoginName"].ToString();
                model.UnitCode = dr["UnitCode"].ToString();
                list.Add(model);
            }
            return list;
        }
        #endregion
    }
}
